﻿/*
 Developed by Deepak Lakhotia
 Developed on: 09/07/2015
*/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using System.IO;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }

        //List of all datatype mappings... List pulled from "https://msdn.microsoft.com/en-IN/library/ms151817.aspx"...
        public string data_type_change(string s1)
        {
            if (s1 == "BFILE") return "VARBINARY";
            else if (s1 == "BLOB") return "VARBINARY";
            else if (s1 == "CHAR") return "CHAR";
            else if (s1 == "CLOB") return "VARCHAR";
            else if (s1 == "DATE") return "DATETIME";
            else if (s1 == "FLOAT") return "FLOAT";
            else if (s1 == "INT") return "NUMERIC";
            else if (s1 == "INTERVAL") return "DATETIME";
            else if (s1 == "LONG") return "VARCHAR";
            else if (s1 == "LONG RAW") return "IMAGE";
            else if (s1 == "NCHAR") return "NCHAR";
            else if (s1 == "NCLOB") return "NVARCHAR";
            else if (s1 == "NUMBER") return "FLOAT";
            else if (s1 == "NVARCHAR2") return "NVARCHAR";
            else if (s1 == "RAW") return "VARBINARY";
            else if (s1 == "REAL") return "FLOAT";
            else if (s1 == "ROWID") return "CHAR";
            else if (s1 == "TIMESTAMP") return "VARCHAR";
            else if (s1 == "UROWID") return "CHAR";
            else if (s1 == "VARCHAR2") return "VARCHAR";
            //If there is no equivalent data type set it to NVARCHAR...
            else return "NVARCHAR";
        }

        private void button1_Click(object sender, EventArgs e)
        {
              //File location where you would like to log down all the table names which moved without any issues...
            using (StreamWriter sw = new StreamWriter(textBox6.Text, true))
            {
                sw.WriteLine(DateTime.Now);
                sw.WriteLine("===================================");
                using (OracleConnection con = new OracleConnection())
                {

                    //Oracle Connection String...
                    con.ConnectionString = textBox3.Text;
                    con.Open();
                    using (OracleConnection con_oracle = con)
                    using (OracleCommand oc = con_oracle.CreateCommand())
                    using (SqlConnection sql_conn = new SqlConnection())
                    {

                        //SQL Connection String...
                        sql_conn.ConnectionString = textBox4.Text;
                        sql_conn.Open();

                        using (OracleDataAdapter adap = new OracleDataAdapter("select * from all_tables", con))
                        {

                            using (DataTable dt = new DataTable())
                            {
                                adap.Fill(dt);
                                List<string> s = new List<string>();
                                List<string> dataMovementIssue = new List<string>();
                                string conn_string = null;
                                sw.WriteLine("Total Number of tables on the Oracle side: " + dt.Rows.Count.ToString());
                                sw.WriteLine();
                                sw.WriteLine("<<<<<<<<<<<< Tables Moved Successfully >>>>>>>>>>>");
                                progressBar1.Visible = true;
                                progressBar1.Value = 0;
                                progressBar1.Maximum = dt.Rows.Count;

                                //Pull each table one by one and perform the below operation...
                                foreach (DataRow row in dt.Rows)
                                {
                                    using (OracleDataAdapter adap1 = new OracleDataAdapter("select * from all_tab_columns where table_name = '" + row["table_name"].ToString() + "'", con))
                                    {
                                        try
                                        {
                                            using (DataTable dt1 = new DataTable())
                                            {
                                                progressBar1.Value++;
                                                adap1.Fill(dt1);

                                                //Specify the database on the SQL side where you would like to move the oracle tables to. Here is is called "test"...
                                                //String s1 is used to build the query to create the table...
                                                string s1 = "use " + textBox5.Text + " ; create table " + row["table_name"] + "(";
                                                //Take each column one by one and check for the data type mappings and set column size accordingly... 
                                                foreach (DataRow row1 in dt1.Rows)
                                                {
                                                    s1 += row1["column_name"].ToString() + " ";
                                                    s1 += data_type_change(row1["data_type"].ToString());
                                                    if (data_type_change(row1["data_type"].ToString()) == "DATETIME")
                                                        s1 += ",";
                                                    else if (data_type_change(row1["data_type"].ToString()) == "IMAGE")
                                                        s1 += ",";
                                                    else if (row1["data_length"].ToString() == "0")
                                                        s1 += " (" + "MAX" + "),";
                                                    else if (row1["data_length"].ToString() != "")
                                                        s1 += " (" + row1["data_length"] + "),";
                                                    //If the datatype is set to the NVARCHAR when there is no equivalent datatype or the data size is incorrect set data-size to MAX...
                                                    else
                                                        s1 += " (" + "MAX" + "),";
                                                }

                                                conn_string = (s1.Substring(0, s1.Length - 1) + ")");
                                                using (SqlCommand comm = new SqlCommand(conn_string, sql_conn))
                                                //If table already exists, drop it...
                                                using (SqlCommand comm1 = new SqlCommand("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" + row["table_name"] + "') AND TYPE IN (N'U'))DROP TABLE " + row["table_name"], sql_conn))
                                                {
                                                    comm1.ExecuteNonQuery();
                                                    comm.ExecuteNonQuery();

                                                    oc.CommandText = null;
                                                    oc.CommandText = "select * from " + row["owner"] + "." + row["table_name"].ToString();

                                                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sql_conn))
                                                    {
                                                        try
                                                        {
                                                            bulkCopy.DestinationTableName = row["table_name"].ToString();
                                                            var data = oc.ExecuteReader();
                                                            bulkCopy.WriteToServer(data);
                                                            listBox1.Items.Add(row["table_name"]);
                                                            sw.WriteLine(row["table_name"].ToString());
                                                        }
                                                        catch (Exception ex)
                                                        {
                                                            if (!dataMovementIssue.Contains(row["table_name"].ToString()))
                                                            {
                                                                dataMovementIssue.Add(row["table_name"].ToString());
                                                                dataMovementIssue.Add("Exception: " + ex.ToString().Substring(0, ex.ToString().IndexOf("\n")));
                                                            }
                                                        }
                                                    }

                                                }
                                            }
                                        }

                                        catch (SqlException ex)
                                        {
                                            if (!s.Contains(row["table_name"].ToString()) && !listBox1.Items.Contains(row["table_name"].ToString()))
                                            {
                                                s.Add(row["table_name"].ToString());
                                                s.Add(conn_string);
                                                s.Add("Exception: " + ex.ToString().Substring(0, ex.ToString().IndexOf("\n")));
                                            }

                                            continue;
                                        }
                                        catch (Exception ex)
                                        {
                                            if (!s.Contains(row["table_name"].ToString()))
                                            {
                                                s.Add(row["table_name"].ToString());
                                                s.Add(conn_string);
                                                s.Add("Exception: " + ex.ToString().Substring(0, ex.ToString().IndexOf("\n")));
                                            }

                                            continue;
                                        }
                                    }

                                }
                                //Perform all the equivalent writeups on the log file and the UI...
                                label7.Text = "Migration Complete";
                                textBox1.Text = (listBox1.Items.Count + (dataMovementIssue.Count / 2)).ToString();
                                textBox8.Text = dt.Rows.Count.ToString();
                                textBox10.Text = (dt.Rows.Count - (listBox1.Items.Count + (dataMovementIssue.Count / 2))).ToString();
                                sw.WriteLine();
                                sw.WriteLine("Count of Tables  Moved Successfully= " + textBox1.Text);
                                sw.WriteLine();
                                sw.WriteLine("<<<<<<<<<< Tables which had issues during data migration >>>>>>>>>>");
                                sw.WriteLine("Table Name, Exception occured while creation.");
                                sw.WriteLine();
                                foreach (string a in dataMovementIssue)
                                {
                                    sw.WriteLine(a.ToString());
                                    if ((dataMovementIssue.IndexOf(a) + 1) % 2 == 0)
                                    {
                                        sw.WriteLine();
                                    }
                                }
                                sw.WriteLine("Count of Tables which had issues during data migration = " + (dataMovementIssue.Count / 2).ToString());
                                sw.WriteLine();
                                sw.WriteLine("<<<<<<<<<< Tables which could not be moved successfully >>>>>>>>>>");
                                sw.WriteLine("Table Name, Query used against SQL Server for the creation of the table, Exception occured while creation.");
                                sw.WriteLine();
                                foreach (string a in s)
                                {
                                    sw.WriteLine(a.ToString());
                                    if ((s.IndexOf(a) + 1) % 3 == 0)
                                    {
                                        sw.WriteLine();
                                    }
                                }
                                sw.WriteLine();
                                sw.WriteLine("Count of Tables which could not be moved successfully= " + textBox10.Text);
                                sw.WriteLine();
                                sw.WriteLine();
                                sw.WriteLine("Summary");
                                sw.WriteLine("====================");
                                sw.WriteLine("Total Number of tables on the Oracle side= " + dt.Rows.Count.ToString());
                                sw.WriteLine("----------------------------------------------------------------------");
                                sw.WriteLine("Count of Tables Moved Successfully with both Schema and Data= " + listBox1.Items.Count.ToString());
                                sw.WriteLine("Count of Tables Moved Successfully with only Schema= " + (dataMovementIssue.Count / 2).ToString());
                                sw.WriteLine("----------------------------------------------------------------------");
                                sw.WriteLine("Total Count of Tables Moved Successfully= " + textBox1.Text);
                                sw.WriteLine("Count of Tables which could not be moved successfully= " + textBox10.Text);
                                sw.WriteLine();
                                sw.WriteLine("<<<<<<<<<<<<<<<<<<<<<<  END  >>>>>>>>>>>>>>>>>>>>");
                                sw.WriteLine();

                            }
                        }
                    }
                }
            }
        }
    }
}

////*********   Program Ends   *********////